#include "sql.h"

sqlite3* mysql_open(const char* dbName)
{
    int rec;
    sqlite3* db;
    rec = sqlite3_open(dbName, &db);
    if (rec != SQLITE_OK)
    {
        fprintf(stderr, "%s\n", sqlite3_errmsg(db));
        exit(EXIT_FAILURE);
    }

    printf("打开数据库成功\n");
    return db;
}

int mysql_create(sqlite3* db, const char* tableName)
{
    char* errmsg;
    char sql[512] = { 0 };
    sprintf(sql, "create table if not exists %s(id int,name text,sex text,score int);", tableName);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    else
    {
        printf("创建表 %s 成功\n", tableName);
        return 0;
    }
}

int mysql_insert(sqlite3* db, const char* tableName, dataStruct data)
{
    char sql[512] = { 0 };
    char* errmsg;
    sprintf(sql, "insert into %s values(%d,'%s','%s',%d);", tableName, data.id, data.name, data.sex, data.score);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    else
    {
        printf("%s信息插入成功\n", data.name);
        return 0;
    }
}


int mysql_delete_name(sqlite3* db, const char* tableName, const char* name)
{
    char sql[512] = { 0 };
    char* errmsg;
    sprintf(sql, "delete from %s where name = '%s';", tableName, name);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    else
    {
        printf("删除%s成功\n", name);
        return 0;
    }
}

int mysql_update(sqlite3* db, const char* tableName, const char* name, int score)
{
    char sql[512] = { 0 };
    char* errmsg;
    sprintf(sql, "update %s set score = %d where name = '%s';", tableName, score, name);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "修改%s的成绩失败\n", name);
        sqlite3_free(errmsg);
        return -1;
    }
    return 0;
}

int mysql_callback(void* data, int colNum, char** argv, char** colName)
{
    if (data != NULL)
    {
        dataStruct* tmpdata = (dataStruct*)data;
        tmpdata->id = atoi(argv[0]);
        strcpy(tmpdata->name, argv[1]);
        strcpy(tmpdata->sex, argv[2]);
        tmpdata->score = atoi(argv[3]);
    }

    for (int i = 0;i < colNum; i++)
    {
        printf("%s = %s\t", colName[i], argv[i]);
    }
    printf("\n");
    return 0;
}

dataStruct mysql_find(sqlite3* db, const char* tableName, const char* name)
{
    dataStruct tmpData;
    tmpData.id = -1;
    strcpy(tmpData.name, "未知");
    strcpy(tmpData.sex, "未知");
    tmpData.score = -1;

    char sql[512] = { 0 };
    char* errmsg;
    sprintf(sql, "select * from %s where name = '%s';", tableName, name);
    if (sqlite3_exec(db, sql, mysql_callback, &tmpData, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "查无此人\n");
        tmpData.id = -1;
    }

    return tmpData;
}


int mysql_show(sqlite3* db, const char* tableName)
{
    char sql[512] = { 0 };
    char* errmsg;
    sprintf(sql, "select * from %s;", tableName);
    if (sqlite3_exec(db, sql, mysql_callback, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "显示失败或者没有用户数据\n");
        return -1;
    }

    return 0;
}

void mysql_close(sqlite3* db)
{
    if (db != NULL)
    {
        sqlite3_close(db);
        printf("数据库关闭成功\n");
    }
}
